import json
import re
import sqlite3


class DbUtils:
    conn = sqlite3.connect('blue_bisect.db', check_same_thread=False)

    def __init__(self, log):
        self.log = log

    def query_active_testcases(self):
        """
        支持二分的用例查询
        :return:
        """
        self.log.info('查询支持二分的用例')
        sql = 'SELECT testcase FROM tb_testcases WHERE active=1'
        self.log.info(sql)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        rows = cursor.fetchall()
        cursor.close()
        testcases = [row[0] for row in rows]
        return testcases

    def query_repo_probability(self, testcase):
        """
        查询用例可能由引起的仓
        :param testcase:
        :return:
        """
        self.log.info(f'查询用例{testcase}失败可能由哪些仓的提交导致')
        sql = f"SELECT repo, COUNT(*) as count FROM tb_bisect_records WHERE testcase = '{testcase}' GROUP BY repo ORDER BY count DESC"
        self.log.info(sql)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        rows = cursor.fetchall()
        cursor.close()
        repos = [row[0] for row in rows]
        self.log.info(repos)
        return iter(repos)

    def query_latest_cause_pr(self, testcase):
        """
        查询该用例最近一次的问题pr,目的：如果这笔pr在需要二分的时间范围内，可以直接得出结论，不用往下分了
        :param testcase:
        :return:
        """
        self.log.info(f'查询用例{testcase}最近一次二分到的pr...')
        sql = f"SELECT * FROM tb_bisect_records WHERE testcase='{testcase}' ORDER BY merge_time DESC LIMIT 1"
        self.log.info(sql)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        row = cursor.fetchone()
        if row:
            columns = [col[0] for col in cursor.description]
            row_dict = dict(zip(columns, row))
            return row_dict
        else:
            return {}

    def upload_bisect_result(self, testcase, pr_dict):
        """
        记录二分结果
        :param testcase:
        :param pr_dict:
        :return:
        """
        self.log.info(f'上传二分结果')
        pr = pr_dict.get('pr', '')
        merge_time = pr_dict.get('merge_time', '')
        issue = pr_dict.get('issue', '')
        if issue:
            repo = re.search(r'gitee.com/openharmony/(?P<repo>\S+?)/issues', issue).group('repo')
        else:
            repo = ''
        cursor = self.conn.cursor()
        sql = f"INSERT INTO tb_bisect_records (testcase, pr, repo, merge_time) VALUES ('{testcase}', '{pr}', '{repo}', '{merge_time}')"
        self.log.info(sql)
        cursor.execute(sql)
        self.conn.commit()
        cursor.close()

    def upload_pipeline_result(self, build_time, result):
        """
        归档流水线自动化测试结果
        :param build_time:
        :param result:
        :return:
        """
        self.log.info(f'上传构建时间为{build_time}的流水线测试结果到数据库')
        pipeline_result = self.query_pipeline_autotest_result(build_time)
        if not pipeline_result:
            sql = f"INSERT INTO tb_pipeline_result (build_time, result) VALUES ('{build_time}', '{json.dumps(result)}')"
        else:
            pipeline_result.update(result)
            sql = f"UPDATE tb_pipeline_result SET result='{json.dumps(pipeline_result)}' WHERE build_time='{build_time}'"
        # self.log.info(sql)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        cursor.close()

    def query_pipeline_autotest_result(self, build_time):
        self.log.info('查询{}时构建的流水线测试结果'.format(build_time))
        sql = f"SELECT result FROM tb_pipeline_result WHERE build_time='{build_time}'"
        self.log.info(sql)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        row = cursor.fetchone()
        cursor.close()
        if not row:
            return {}
        return json.loads(row[0])

    def delete_latest_pipeline_result(self):
        self.log.info('删除最新的流水线测试结果')
        sql = "SELECT id FROM tb_pipeline_result ORDER BY build_time DESC LIMIT 1"
        self.log.info(sql)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        row = cursor.fetchone()
        id_ = row[0]
        delete_sql = f'DELETE FROM tb_pipeline_result WHERE id={id_}'
        self.log.info(sql)
        cursor.execute(delete_sql)
        cursor.close()
